Zendeskの1週間分のチケットをFivetranとSnowflakeを使って抽出してみた
こんにちは、スズです。
私が所属しているデータアナリティクス事業本部では、Zendeskを使ってサポートを提供しています。Zendeskには日々新しいチケットが起票され、対応しているとあっという間に1週間が過ぎていきます。そんな1週間を振り返るべく、Fivetran、Snowflakeを使って、直近1週間にZendeskで起票されたチケットの「起票日」「件名」「企業名」の抽出に挑戦してみました。
前提条件と環境
今回使用した環境の前提条件は以下の通りとなります。
- Zendeskはサンドボックスを使用
- サンドボックスにはテスト用としていくつかチケットを起票済み
- FivetranのDestinationにSnowflakeを設定済み
ZendeskのサンドボックスからFivetranを経由してSnowflakeにデータを送る、という環境を作りました。
FivetranでZendeskと連携
FivetranにはすでにDestinationとしてSnowflakeが設定されています。こちらのConnectorsに新しくZendeskを追加します。
Connectorsの右上にあるCONNECTORをクリックします。
ソースの選択画面が表示されます。ソースの数が多いので、「Zendesk」を検索、Zendesk Supportを見つけて選択します。
Zendesk Supportの設定では、Destination SchemaとDomainを設定します。Destination SchemaはConnectorsに表示される任意の名前（Snowflakeで作成されるスキーマの名前）、Domainは接続先のZendeskを指定します。設定後、AUTHORIZEをクリックして認証します。
認証したのち、SAVE & TESTをクリックします。
テストと保存が完了すると、All connection tests passed!というメッセージが表示されます。VIEW CONNECTORをクリックして作成したConnectorに移動します。
Start Initial SyncをクリックするとZendesk、Snowflakeと初回の接続になります。
初回の接続が完了すると、メールでの通知があります。また、Fivetranの画面は以下のようになりました。
なお、今回は省略しましたが、連携するデータのうち一部はSchemaで有効無効を切り替えることができます。
Snowflakeでビューの作成
初回連携が終わってSnowflakeを見てみると、スキーマが作成されていました。
試しにTICKETのテーブルを見てみると、起票されたチケットのデータが表示されていました。
select * from ticket;
これでチケットのデータをSnowflakeで確認できましたが、カラムが多く、必要な情報を確認するのに探す時間がかかってしまいます。分かりやすく表示するために、「起票日」「件名」「企業名」のみ表示するビューを新しく作ってみます。
TICKETのテーブルには起票日と件名になる「CREATED_AT」「SUBJECT」のカラムがありました。また、「ORGANIZATION_ID」という企業のIDを表すカラムはありましたが、企業名となるカラムはありませんでした。企業名についてはORGANIZATIONのテーブルで参照できました。ORGANIZATIONのテーブルにある「NAME」で企業名、「ID」で企業のIDを確認します。
select * from organization;
TICKETのテーブルからは「CREATED_AT」「SUBJECT」「ORGANIZATION_ID」、ORGANIZATIONのテーブルからは「NAME」「ID」のカラムを使って新しいViewを作成してみます。
まずは「1週間分のチケット」という条件はのぞいて、TICKETテーブルの「CREATED_AT」「SUBJECT」、ORGANIZATIONテーブルの「NAME」を表示するビューを作成してみました。
create view zendesk_sandbox_tickets as select tic.created_at, tic.subject, org.name from ticket tic, organization org where tic.organization_id = org.id;
作成したビューを確認してみると、「CREATED_AT」のタイムゾーンが日本時間ではないようです。
select * from zendesk_sandbox_tickets;
再度ビューを作成します。今度は「CREATED_AT」を日本時間への修正と、条件に「本日の1週間前以上日時」（直近1週間という想定）を追加しています。
タイムゾーンの変更と日付の計算については、Snowflakeのドキュメントを参考にしました。
create view zendesk_sandbox_tickets as
select convert_timezone('Asia/Tokyo', tic.created_at) as CREATED_AT_in_tokyo,
tic.subject, org.name
from ticket tic, organization org
where tic.organization_id = org.id
and CREATED_AT_in_tokyo >= dateadd(week, -1, convert_timezone('Asia/Tokyo', current_timestamp()));
ビューを確認しみると、時間が日本時間に修正されていました。また、チケットの件数も先ほどより減少し、4月に起票されたチケットは表示されていません。
select * from zendesk_sandbox_tickets;
さいごに
今回はZendeskの1週間分のチケットをFivetranとSnowflakeを使って抽出に挑戦してみました。
今回はFivetranとSnowflakeを使ってチケットの一覧で表示することを最終目的にしていますが、さらにLookerと連携した可視化を実現しているエントリもあります。あわせてご参照いただきますと幸いです。
